package hit.dao.database;

import hit.core.HITBase;
import hit.core.HomeInventoryTracker;
import hit.core.Item;
import hit.core.Product;
import hit.core.ProductGroup;
import hit.core.StorageUnit;
import hit.dao.DAOBase;
import hit.dao.DataAccessObject;
import hit.dao.SettingsDAO;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileReader;
import java.nio.file.FileSystemException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 *Data Access Object that maintains persistence with a SQLite database
 */
public class DatabaseDAO implements DataAccessObject{
	
	private final static String DB_FILENAME = "hit.sqlite";

	private Connection connection;
	private DBStorageUnitDAO storageUnitDAO;
	private DBProductGroupDAO productGroupDAO;
	private DBProductDAO productDAO;
	private DBItemDAO itemDAO;
	private DBSettingsDAO settingsDAO;
	
	/**
	 * Load the classes for the sqlite driver
	 * */
	public static void Initialize() {
		// Load the SQLite database driver
		try {
			final String driver = "org.sqlite.JDBC";
			Class.forName(driver);
		}
		catch(ClassNotFoundException e) {
			e.printStackTrace();
		}
	}
	
	public DatabaseDAO(){
		
		// Check to see if the db file exists. If not, create it.
		File dbFile = new File(DB_FILENAME);
		
		if (!dbFile.exists())
		{
			CreateDatabase();
		} 
		//*/
		
		storageUnitDAO = new DBStorageUnitDAO(this);
		productGroupDAO = new DBProductGroupDAO(this);
		productDAO = new DBProductDAO(this);
		itemDAO = new DBItemDAO(this);
		settingsDAO = new DBSettingsDAO(this);
	}
	
	/**
	 * Load all the data in the HIT from the last use.
	 * @return the loaded HomeInventoryTracker
	 */
	@Override
	public HomeInventoryTracker Load() {
		
		this.StartTransaction();
		
		HomeInventoryTracker hit = HomeInventoryTracker.GetEmptyInstance();
		
		List<StorageUnit> units = LoadStorageUnits(hit);
		List<ProductGroup> groups = LoadProductGroups(units);
		List<Product> products = LoadProducts(hit, units, groups);
		LoadItems(hit, units, groups, products);
		LoadSettings(hit);
		
		this.EndTransaction(true);
		this.CloseConnection();
		
		return hit;
	}

	private void LoadSettings(HomeInventoryTracker hit) {
		// TODO Auto-generated method stub
		try
		{
			this.GetSettingsDAO().GetAll(hit);
		}
		catch (Exception ex)
		{
			ex.printStackTrace();
		}
		
	}

	private void LoadItems(HomeInventoryTracker hit, List<StorageUnit> units,
			List<ProductGroup> groups, List<Product> products) {
		List<HITBase> result = itemDAO.GetAll();
		List<Item> items = new ArrayList<Item>();
		for (HITBase base: result) {
			Item item = (Item) base;
			items.add(item);
			
			//find which product it belongs to
			for (Product product: products) {
				if (product.getId() == item.GetProductID())
					item.SetProduct(product);
			}
			
			//find the container it belongs to
			for (StorageUnit unit: units) {
				if (unit.getId() == item.GetContainerID())
					item.SetContainer(unit);
			}
			
			for (ProductGroup group: groups) {
				if (group.getId() == item.GetContainerID())
					item.SetContainer(group);
			}
			
			//add the item if it hasn't been removed
			if (item.GetExit() == null)
				hit.GetItemIndex().AddItem(item);
			//if it's a removed item, remove it
			else
				hit.GetItemIndex().AddToRemovedIndex(item);
		}
	}

	private List<Product> LoadProducts(HomeInventoryTracker hit,
			List<StorageUnit> units, List<ProductGroup> groups) {
		//load the products
		List<HITBase> result = productDAO.GetAll();
		List<Product> products = new ArrayList<Product>();
		for (HITBase base: result) {
			Product product = (Product) base;
			products.add(product);
			
			//add the product to the product index
			hit.GetProductIndex().AddProduct(product);
			
			//loop over all containers, adding the product to them if the id matches
			for (Integer i: product.GetContainerIDs()) {
				for (StorageUnit unit: units) {
					if (i == unit.getId()) {
						hit.GetProductIndex().AddProductToContainer(product, unit);
					}
				}
				
				for (ProductGroup group: groups) {
					if (i == group.getId()) {
						hit.GetProductIndex().AddProductToContainer(product, group);
					}
				}
			}
		}
		
		return products;
	}

	private List<ProductGroup> LoadProductGroups(List<StorageUnit> units) {
		//add the product groups
		List<HITBase> result = productGroupDAO.GetAll();
		List<ProductGroup> groups = new ArrayList<ProductGroup>();
		for (HITBase base: result) {
			ProductGroup group = (ProductGroup) base;
			groups.add(group);
			
			//loop through storageunits looking for the matching parent unit
			for (StorageUnit unit: units) {
				if (group.GetParentID() == unit.getId()) {
					group.SetParentContainer(unit);
					unit.AddProductGroup(group);
				}
			}
			
			//if we've found the parent container, continue to next iteration
			if (group.GetParentContainer() != null) {
				continue;
			}
			
			//if we still haven't found it, look through the product groups
			for (HITBase base2: result) {
				ProductGroup group2 = (ProductGroup) base2;
				if (group.GetParentID() == group2.getId()) {
					group.SetParentContainer(group2);
					group2.AddProductGroup(group);
				}
			}
		}
		
		return groups;
	}

	private List<StorageUnit> LoadStorageUnits(HomeInventoryTracker hit) {
		//add the storage units
		List<HITBase> result = storageUnitDAO.GetAll();
		List<StorageUnit> units = new ArrayList<StorageUnit>();
		for (HITBase base: result) {
			StorageUnit unit = (StorageUnit) base;
			hit.AddStorageUnit(unit);
			units.add(unit);
		}
		return units;
	}

	/**
	 * Save all the data in the HIT
	 * @param hit the HomeInventoryTracker to save
	 */
	@Override
	public void Close(HomeInventoryTracker hit) {
		return;
	}

	/**
	 *
	 * @return the DAO that handles all StorageUnit operations
	 */
	@Override
	public DAOBase GetStorageUnitDAO() {
		return storageUnitDAO;
	}

	/**
	 *
	 * @return the DAO that handles all ProductGroup operations
	 */
	@Override
	public DAOBase GetProductGroupDAO() {
		return productGroupDAO;
	}

	/**
	 *
	 * @return the DAO that handles all Product Operations
	 */
	@Override
	public DAOBase GetProductDAO() {
		return productDAO;
	}

	/**
	 *
	 * @return the DAO that handles all Item operations
	 */
	@Override
	public DAOBase GetItemDAO() {
		return itemDAO;
	}

	/**
	 *
	 * @return the DAO that handles all HIT settings
	 */
	@Override
	public SettingsDAO GetSettingsDAO() {
		return settingsDAO;
	}
	
	/**
	 * Starts a database transaction with the open {@link Connection}.
	 */
	public void StartTransaction(){
		// Open a connection to the database and start a transaction
		String connectionURL = "jdbc:sqlite:" + DB_FILENAME;
		
		try {
		    // Open a database connection
			connection = DriverManager.getConnection(connectionURL);
		
			// Start a transaction
			connection.setAutoCommit(false);
		}
		catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	private void CreateDatabase()
	{
		String[] createQueries = GetCreateDatabaseQueries();
		
		try {
			String connectionURL = "jdbc:sqlite:" + DB_FILENAME;
			connection = DriverManager.getConnection(connectionURL);
			
			// Start a transaction
			connection.setAutoCommit(false);
		    
			try {
				for (String createQuery : createQueries)
				{
					PreparedStatement stmt = connection.prepareStatement(createQuery);
					stmt.executeUpdate();
			    	connection.commit();
				}
		    }
		    finally {//close up resources
			    connection.close();
			}
		}
		catch (SQLException e) {
		    e.printStackTrace();
		    assert(false);
		}
	}
	
	private String[] GetCreateDatabaseQueries(){
		
		String createQuery = "";
		
		File file = new File("create.sql");
		
		try
		{
			if (!file.exists())
				throw new FileSystemException("Missing Database Creation file. Cannot continue.");
			
			FileReader reader = new FileReader(file);
			BufferedReader bufferedRead = new BufferedReader(reader);
			
			String line;
			while (( line = bufferedRead.readLine() ) != null)
			{
				createQuery += line;
			}
			
			bufferedRead.close();
			reader.close();
			
			return createQuery.split(";");
		}
		catch (Exception ex)
		{
			ex.printStackTrace();
			assert(false); // exit with failure.
		}
		
		return null;
	}
	
	/**
	 * Ends the open transaction.
	 * @param commit Commit changes if True. Do nothing otherwise.
	 */
	public void EndTransaction(boolean commit){
		// Commit or rollback the transaction and close the connection
		try {
	    	if (commit)
		        connection.commit();
		    else
		        connection.rollback();
	    
		}
		catch (SQLException e) {
		    e.printStackTrace();
		}
	}

	public void CloseConnection() {
		try {
			if (connection != null)
				connection.close();
		}
		catch (SQLException e) {
		    e.printStackTrace();
		}
		
		connection = null;
	}
	
	/**
	 *
	 * @return The database {@link Connection}
	 */
	public Connection GetConnection(){
		return connection;
	}
}
